﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using FirebirdSql.Data.FirebirdClient;
using System.Data;

namespace SendMailApplication
{
    public class Common
    {
        public static string connectionString =
            "User=" + SendMailApplication.Properties.Settings.Default.User + ";" +
            "Password=" + SendMailApplication.Properties.Settings.Default.Password + ";" +
            "Database=" + SendMailApplication.Properties.Settings.Default.Database + ";" +
            "DataSource=" + SendMailApplication.Properties.Settings.Default.DataSource + ";" +
            "Port=" + SendMailApplication.Properties.Settings.Default.Port + ";" +
            "Dialect=3;" +
            "Charset=NONE;" +
            "Role=;" +
            "Connection lifetime=15;" +
            "Pooling=true;" +
            "MinPoolSize=0;" +
            "MaxPoolSize=50;" +
            "Packet Size=8192;" +
            "ServerType=0";

        public static int excuteNonQuery(string command)
        {
            // Set the ServerType to 1 for connect to the embedded server
            int err = -1;
            FbConnection myConnection = new FbConnection(connectionString);

            try
            {

                myConnection.Open();

                FbTransaction myTransaction = myConnection.BeginTransaction();

                FbCommand myCommand = new FbCommand();

                myCommand.CommandText = command;
                myCommand.Connection = myConnection;
                myCommand.Transaction = myTransaction;

                // Execute Update
                err = myCommand.ExecuteNonQuery();

                // Commit changes
                myTransaction.Commit();

                // Free command resources in Firebird Server
                myCommand.Dispose();

            }
            catch (Exception ex)
            {

            }
            finally
            {

                // Close connection
                myConnection.Close();
            }

            return err;

        }

        public static List<object> callProcedureQuery(String name, FbParameterCollection parameterCollection, SendMailApplication.Helper.IParseObj parseObj)
        {
            using (FbConnection conn = new FbConnection(connectionString))
            {
                conn.Open();
                using (FbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = name;
                    cmd.CommandType = CommandType.StoredProcedure;
                    if (parameterCollection != null)
                    {
                        foreach (object pr in parameterCollection)
                        {
                            cmd.Parameters.Add(pr);
                        }
                    }
                    using (FbDataReader dr = cmd.ExecuteReader())
                    {
                        return parseObj.excute(dr);
                    }
                }
            }
        }

        public static int callProcedureQuery(String name, FbParameterCollection parameterCollection)
        {
            using (FbConnection conn = new FbConnection(connectionString))
            {
                conn.Open();
                using (FbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = name;
                    cmd.CommandType = CommandType.StoredProcedure;
                    if (parameterCollection != null)
                    {
                        foreach (object pr in parameterCollection)
                        {
                            cmd.Parameters.Add(pr);
                        }
                    }
                    return cmd.ExecuteNonQuery();
                }
            }
        }
    }
}